package com.adu.music.db;

import com.adu.music.bean.Proxy;
import com.adu.music.util.DbUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;

/**
 * @author duchuanchuan
 * @date 2017/1/6
 */
public class ProxyDao {

    private Logger logger = LoggerFactory.getLogger(ProxyDao.class);

    private ProxyDao() {
    }

    private static ProxyDao proxyDao = new ProxyDao();

    public static ProxyDao getInstance() {
        return proxyDao;
    }

    /*public void save(Proxy proxy) {
        String sql = "insert into proxy(ip,port,type,getpost,speed,priority, hashcode,created_at,updated_at) values(?,?,?,?,?,?,?,now(),now());";
        DbUtils.getJdbcTemplate().update(sql, preparedStatement -> {
            preparedStatement.setString(1, proxy.getIp());
            preparedStatement.setInt(2, proxy.getPort());
            preparedStatement.setString(3, proxy.getType());
            preparedStatement.setString(4, proxy.getGetpost());
            preparedStatement.setDouble(5, proxy.getSpeed());
            preparedStatement.setInt(6, proxy.getPriority());
            preparedStatement.setInt(7, proxy.hashCode());
        });
    }*/

    public void batchSave(List<Proxy> proxyList) {
        String sql = "insert into proxy(ip,port,type,getpost,speed,priority, hashcode,created_at,updated_at) values(?,?,?,?,?,?,?,now(),now());";
        if (proxyList.size() == 0) return;
        int[] ints = DbUtils.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                Proxy proxy = proxyList.get(i);
                preparedStatement.setString(1, proxy.getIp());
                preparedStatement.setInt(2, proxy.getPort());
                preparedStatement.setString(3, proxy.getType());
                preparedStatement.setString(4, proxy.getGetpost());
                preparedStatement.setDouble(5, proxy.getSpeed());
                preparedStatement.setInt(6, proxy.getPriority());
                preparedStatement.setInt(7, proxy.hashCode());
            }

            @Override
            public int getBatchSize() {
                return proxyList.size();
            }
        });
        logger.info("ProxyDao batch insert into {} cloumns ", ints.length);
    }

    public List<Proxy> queryByIndex(int offset, int size) {
        String sql = "select * from proxy where priority > -1 order by created_at desc limit " + offset + "," + size;
        return DbUtils.getJdbcTemplate().query(sql, (resultSet, i) -> {
            Proxy proxy = new Proxy();
            proxy.setId(resultSet.getInt("id"));
            proxy.setIp(resultSet.getString("ip"));
            proxy.setPort(resultSet.getInt("port"));
            proxy.setGetpost(resultSet.getString("getpost"));
            proxy.setSpeed(resultSet.getDouble("speed"));
            proxy.setPriority(resultSet.getInt("priority"));
            proxy.setType(resultSet.getString("type"));
            return proxy;
        });
    }

    public void updateProxyPriority(Proxy proxy) {
        String sql = "update proxy set priority=" + proxy.getPriority() + ", updated_at=now() where id=" + proxy.getId();
        DbUtils.getJdbcTemplate().update(sql);
    }

    public void batchUpdateProxyPriority(Collection<Proxy> proxys) {
        String sql = "update proxy set priority=?,updated_at=now() where id=?";
        DbUtils.getJdbcTemplate().batchUpdate(sql, proxys, proxys.size(), (preparedStatement, proxy) -> {
            preparedStatement.setInt(1, proxy.getPriority());
            preparedStatement.setInt(2, proxy.getId());
        });
    }

    public boolean hashcodeExist(int hashcode) {
        String sql = "select id from proxy where hashcode=" + hashcode;
        return DbUtils.getJdbcTemplate().queryForList(sql).size() > 0;
    }

}
